# coding=UTF-8
'''
Created on 2020-07-10 17:53
@author: jin
'''
import MySQLdb
from lxml import etree
import requests
import json
import time
from decimal import Decimal
import re

def get_data_from_json(url, data_type):
    response = requests.request('get', url)
    print(response.encoding)
    json_text = response.text;
    type = typeof(json_text)
    if (type == "str"):
        json_text = json_text[json_text.index('['):json_text.index(']') + 1]
    return json.loads(json_text)




def typeof(variate):
    type = None
    if isinstance(variate, int):
        type = "int"
    elif isinstance(variate, str):
        type = "str"
    elif isinstance(variate, float):
        type = "float"
    elif isinstance(variate, list):
        type = "list"
    elif isinstance(variate, tuple):
        type = "tuple"
    elif isinstance(variate, dict):
        type = "dict"
    elif isinstance(variate, set):
        type = "set"
    return type


def save_data(db, sql):
    print(sql)
    cursor = db.cursor()
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()


def find_data(db, sql):
    # print(sql)
    cursor = db.cursor()
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        # 打印结果
        return results
    except:
        print("Error: unable to fecth data")


def get_data_to_mysql(data, db, qz, data_type, bz):
    for value in data:
        values = {}
        if (typeof(data_type) == 'dict'):
            values = value
            print(value)
            if 'FCODE' not in value.keys():
                continue
            if 'SHORTNAME' not in value.keys():
                value['SHORTNAME'] = ''
            if 'DWJZ' not in value.keys():
                value['DWJZ'] = ''
            if 'FUNDTYPE' not in value.keys():
                value['FUNDTYPE'] = ''
            if 'type_zh' not in value.keys():
                value['type_zh'] = ''
            else:
                value['type_zh'] = ''
        if (typeof(data_type) == 'list'):
            v = value.split(',')
            list_to_dict(data_type, v, values,'FCODE',0)
            list_to_dict(data_type, v, values,'SHORTNAME',1)
            list_to_dict(data_type, v, values,'DWJZ',2)
            list_to_dict(data_type, v, values,'FUNDTYPE',3)
            list_to_dict(data_type, v, values,'type_zh',4)
        row_save(db, qz, values, bz)


def list_to_dict(data_type, v, value,str,i):
    if (typeof(data_type[i]) == 'int'):
        value[str] = v[data_type[i]]
    else:
        value[str] = data_type[i]


def row_save(db, qz, value, bz):
    if(not value):
        return
    sql = "select * from my_fund_good where fund_code='%s'" % (value["FCODE"])
    sql_data = find_data(db, sql)
    if (sql_data):
        plan = sql_data[0][3] + Decimal(str(qz))
        print(str(sql_data[0][3])+","+str(Decimal(str(qz)))+"="+str(plan))
        bz1 = bz
        if (sql_data[0][4]):
            bz1 = sql_data[0][4] + bz
        type_zh=value["type_zh"]
        if( value["type_zh"] not in sql_data[0][5]):
            type_zh=sql_data[0][5]+","+type_zh
        sql = "UPDATE my_fund_good SET plan = '%s',bz= '%s' ,update_date='%s' ,price='%s',type_zh='%s' WHERE fund_code = '%s'" \
              % (plan, bz1, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), value["DWJZ"],type_zh, value["FCODE"])
    else:
        sql = """INSERT INTO my_fund_good(fund_code,fund_name,
         plan, bz, update_date, price,type,type_zh)
         VALUES ('%s', '%s',%s, '%s', '%s', '%s','%s','%s')""" % (value["FCODE"], value["SHORTNAME"], qz, bz,
                                                                  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                                                                  value["DWJZ"], value["FUNDTYPE"],value["type_zh"])
    save_data(db, sql)


def get_data_from_html(url,codes):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36'
    }
    response = requests.request('get', url, headers=headers)
    print(response.encoding)
    fund_xpath = etree.HTML(response.content, parser=etree.HTMLParser(encoding='utf8'))
    risk_grade=fund_xpath.xpath('//div[contains(@class,"fxdj")]/div[@class="fivebar"]/ul/li/span[contains(@class,"chooseLow")]/text()')
    fxtb_bzc =fund_xpath.xpath('//table[@class="fxtb"]/tr[2]/td[contains(@class,"num")]/text()')
    fxtb_xpbl =fund_xpath.xpath('//table[@class="fxtb"]/tr[3]/td[contains(@class,"num")]/text()')
    try:
        script =str(fund_xpath.xpath('//script[14]/text()')[0])
        scores = re.findall('var Scores = \[(.+)\];',script)[0].split(',')
        ScoresName = re.findall('var ScoresName = \[(.+)\];',script)[0].split(',')
        s ="{%s, %s,%s, %s, %s, %s,%s, %s, %s, %s}"% (ScoresName[0],scores[0],ScoresName[1],scores[1],ScoresName[2],scores[2],ScoresName[3],
                                                      scores[3],ScoresName[4],scores[4])
        s=s.replace('\'','"')
        avg =re.findall('var avg = "(.+)";',script)[0]
        sql ="""INSERT INTO my_analyze_fund_data(fund_code,fund_name,
         risk_grade_all, risk_grade_class,fxtb_bzc1,fxtb_bzc2,fxtb_bzc3,
         fxtb_xpbl1,fxtb_xpbl2,fxtb_xpbl3,scores,fx_avg ,update_date)
         VALUES ('%s', '%s','%s', '%s', '%s', '%s','%s','%s','%s', '%s','%s', '%s', '%s')""" \
             % (codes[0], codes[1], risk_grade[0], risk_grade[1],fxtb_bzc[0],fxtb_bzc[1],fxtb_bzc[2],
                fxtb_xpbl[0],fxtb_xpbl[1],fxtb_xpbl[2],s,avg,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        save_data(db,sql)
    except:
        print("Error: unable to fecth data")

    return ""

if __name__ == "__main__":
    # print(data)
    db = MySQLdb.connect("localhost", "root", "123456", "fund", charset='utf8')
    # sql = """select * from my_fund_good where 1=1 and fund_code not in ('007455')
    # and fund_code not in (select fund_code from my_analyze_fund_data)"""
    # sql_data = find_data(db, sql)
    for codes in (('008281','芯片'),('001027',"大农业"),('160422','创业板50')
                  ,('000596','前海开源中证军工'),('163115','申万菱信中证军工指数分级')):
        url ="http://fundf10.eastmoney.com/tsdata_%s.html" %(codes[0])
        sql = get_data_from_html(url,codes)

    # sql = get_data_from_html("http://fundf10.eastmoney.com/tsdata_001069.html",('001069','华泰柏瑞消费成长混合'))
    # url ="http://fund.eastmoney.com/fundhot8.html"
    # xpaths = {'FCODE': '//div[contains(@class,"clearfix")]/div/table/tbody/tr/td[2]/a/@href'
    #     , 'SHORTNAME': '//div[contains(@class,"clearfix")]/div/table/tbody/tr/td[2]/a/text()'}
    # data = get_data_from_html(url, xpaths)
    # get_data_to_mysql(data, db, 1, 'map', "天天热销基金；")

    # sql = "select * from my_fund_good where fund_code='%s'" % ("210011")
    # sql_data = find_data(db, sql)
    # print(sql_data)
    # sql="""UPDATE my_fund_good SET plan = 1,bz= '天天三年五星；' ,update_date='2020-07-04 08:59:00' ,price='' WHERE fund_code = '210011'"""
    # save_data(db,sql)
